package util;

import bean.person;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StaffDao {
    //增加
    public void addStaff(person staff) throws SQLException {
        Connection conn = DbUtil.getConnection();
        //sql
        String sql="insert into Staff"+"(id,userName,password,name,sex,birth,nation,phone,place)"+"values("+"'"+staff.getId()+"'"+","+"'"+staff.getUserName()+"'"+","+"'"+staff.getPassword()+"'"+","+"'"+staff.getName()+"'"+","+"'"+staff.getSex()+"'"+","+"'"+staff.getBirth()+"'"+","+"'"+staff.getNation()+"'"+","+"'"+staff.getPhone()+"'"+","+"'"+staff.getPlace()+"'"+")";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
        //更新编号
        ptmt = conn.prepareStatement("UPDATE UPDATNO set staffNo='"+staff.getUserName()+"' WHERE No='1'");//更新
        ptmt.execute();

        System.out.println("add staff success!");
    }

    //删除(id)
    public void delStaff(String id) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "delete from Staff where id='"+id+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
    }

    //更新
    public void updateStaff(String userName,String id,String name,String sex,String birth,String nation,String phone,String place) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "UPDATE Staff set name='"+name+"',id='"+id+"',sex='"+sex+"',birth='"+birth+"',nation='"+nation+"',phone='"+phone+"',place='"+place+"' where userName='"+userName+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
    }

    //查one(userName)
    public person get(String userName) throws SQLException {
        person staff = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  Staff where userName='"+userName+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            staff = new person();
            staff.setUserName(rs.getString("userName"));
            staff.setPassword(rs.getString("password"));
            staff.setId(rs.getString("id"));
            staff.setName(rs.getString("name"));
            staff.setSex(rs.getString("sex"));
            staff.setBirth(rs.getString("birth"));
            staff.setNation(rs.getString("nation"));
            staff.setPhone(rs.getString("phone"));
            staff.setPlace(rs.getString("place"));
        }
        return staff;
    }

    //列出所有员工
    public List<person> query() throws SQLException {
        Connection conn = DbUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from  Staff");
        List<person> SList = new ArrayList<person>();
        person staff = null;
        while (rs.next()){
            staff = new person();
            staff.setUserName(rs.getString("userName"));
            staff.setPassword(rs.getString("password"));
            staff.setId(rs.getString("id"));
            staff.setName(rs.getString("name"));
            staff.setSex(rs.getString( "sex"));
            staff.setBirth(rs.getString("birth"));
            staff.setNation(rs.getString("nation"));
            staff.setPhone(rs.getString("phone"));
            staff.setPlace(rs.getString("place"));
            SList.add(staff);
        }
        return SList;
    }

    //搜索框
    public List<person> search(String find) throws SQLException {
        Connection conn = DbUtil.getConnection();
        List<person> SList = new ArrayList<person>();
        Statement stmt = conn.createStatement();
        String[] sql = new String[8];
        sql[0] = "select * from  Staff where userName='"+find+"'";
        sql[1] = "select * from  Staff where id='"+find+"'";
        sql[2] = "select * from  Staff where name='"+find+"'";
        sql[3] = "select * from  Staff where sex='"+find+"'";
        sql[4] = "select * from  Staff where birth='"+find+"'";
        sql[5] = "select * from  Staff where nation='"+find+"'";
        sql[6] = "select * from  Staff where phone='"+find+"'";
        sql[7] = "select * from  Staff where place='"+find+"'";
        for (int i=0;i<8;i++){
            ResultSet rs = stmt.executeQuery(sql[i]);
            while (rs.next()){
                person staff = new person();
                staff.setUserName(rs.getString("userName"));
                staff.setPassword(rs.getString("password"));
                staff.setId(rs.getString("id"));
                staff.setName(rs.getString("name"));
                staff.setSex(rs.getString( "sex"));
                staff.setBirth(rs.getString("birth"));
                staff.setNation(rs.getString("nation"));
                staff.setPhone(rs.getString("phone"));
                staff.setPlace(rs.getString("place"));
                SList.add(staff);
            }
        }
        return SList;
    }

    public String updateStaffNo() throws SQLException {
        //获取已存在的最大（新）staffNo
        String staffNo = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  UPDATNO";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            staffNo = rs.getString("staffNo");
        }
        String one = staffNo.substring(0, 5);
        String two = staffNo.substring(5);
        int num = Integer.parseInt(two);
        num = num+1;
        System.out.println(one + "-" + two);
        staffNo = one + Integer.toString(num);

        System.out.println(staffNo);

        return staffNo;
    }
}
